﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;

namespace DAL
{
    /// <summary>
    ///StockInfoDAL 的摘要说明
    /// </summary>
    public class StockInfoDAL
    {
        public DataTable GetSecuritiesInformation()
        {
            string sql = "select * from tb_public_0007 where f003v_0007='A股'";
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sql, null);
            return tb;
        }

        public DataRow GetAgencyInformationById(string id)
        {
            string sql = "select * from tb_public_0001 where ob_orgid_0001=@id";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", SqlDbType.VarChar, id);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sql, tool.GetSqlParameters());
            if (tb.Rows.Count > 0)
            {
                return tb.Rows[0];
            }
            return null;
        }
        //tb_company_0002
        public DataRow GetBasicInfoCompanies(string id)
        {
            string sql = "select * from tb_company_0002 where ob_orgid_0002=@id";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", SqlDbType.VarChar, id);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sql, tool.GetSqlParameters());
            if (tb.Rows.Count > 0)
                return tb.Rows[0];
            return null;
        }

        public DataTable GetTypes()
        {
            string sql = "select * from tb_text_0031 where  ob_isvalid_0031='1'";
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text,sql, null);
            return tb;
        }

        public DataTable NewsListByTypeId(string id,DateTime? uTime)
        {
            if (uTime == null)
            {
                uTime = DateTime.Now.Date.AddDays(-3);
            }
            string sql = "select * from tb_text_0022 where ob_sortcode_0022=@typeId and OB_RECTIME_0022>=@uTime order by OB_RECTIME_0022 DESC";
            Tools tool = new Tools();
            tool.AddSqlParameter("typeId", SqlDbType.VarChar, id);
            tool.AddSqlParameter("uTime", SqlDbType.DateTime, uTime);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataSet GetNewsById(long newsId)
        {
            string sql = "select * from tb_text_0021 where ob_textid_0021=@newsId;";
            //sql+= "select * from tb_text_0025 where ob_textid_0025=@newsId";
            Tools tool = new Tools();
            tool.AddSqlParameter("newsId", SqlDbType.BigInt, newsId);
            DataSet ds = SqlHelper.ExecuteGetDataSet(DBManager.Cpdb, CommandType.Text, sql, tool.GetSqlParameters());
            return ds;
        }

        public DataTable GetNewsIdReStocks(DateTime? uTime)
        {
            if (uTime == null)
            {
                uTime = DateTime.Now.Date.AddDays(-1);
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select OB_TEXTID_0021,F004D_0021 ");
            sb.AppendLine("from tb_text_0021 t1,tb_text_0026 t6 ");
            sb.AppendLine("where t1.ob_textid_0021=t6.ob_textid_0026 ");
            sb.AppendLine("and F004D_0021>@utime");
            Tools tool = new Tools();
            tool.AddSqlParameter("utime", SqlDbType.DateTime,DateTime.Now.Date);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sb.ToString(), tool.GetSqlParameters());
            return tb;
        }

        public DataTable GetStocksByNewsId(long newsId)
        {
            string sql = "select * from tb_text_0026 where ob_textid_0026="+newsId.ToString();
            Tools tool = new Tools();
            tool.AddSqlParameter("newsId", SqlDbType.BigInt, newsId);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.Cpdb, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }
    }
}